-- Originally called 'proc_stratum_info' by Norm Olsen
-- CREATE PROCEDURE [dbo].[proc_Stratum_Info] @sid INT
-- AS
   SET NOCOUNT ON
   --DECLARE @surveyid INTEGER
   --SET @surveyid = @surveyidval
   SELECT G.GROUPING_CODE,
      COUNT(G.GROUPING_CODE) AS NUMBER_OF_SETS,
      MAX(AREA_KM2) AS AREA_KM2
   FROM TRIP_SURVEY TRS
      INNER JOIN FISHING_EVENT FE ON
      TRS.TRIP_ID = FE.TRIP_ID
      LEFT JOIN TRAWL_SPECS TS ON
      FE.FISHING_EVENT_ID = TS.FISHING_EVENT_ID
      INNER JOIN FISHING_EVENT_GROUPING FEG ON
      FE.FISHING_EVENT_ID = FEG.FISHING_EVENT_ID
      INNER JOIN SURVEY_GROUPING SG ON
      TRS.SURVEY_ID = SG.SURVEY_ID AND
      FEG.GROUPING_CODE = SG.GROUPING_CODE
      INNER JOIN [GROUPING] G ON
      SG.GROUPING_CODE = G.GROUPING_CODE
   WHERE ISNULL(TS.USABILITY_CODE,1) IN (1,2,6) AND TRS.SURVEY_ID IN (@surveyid)
   GROUP BY G.GROUPING_CODE ORDER BY G.GROUPING_CODE

-- getData("gfb_survey_stratum.sql","GFBioSQL",strSpp="396",surveyid=3)

